﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using acessaDB;
using Frame;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Estrutura;


namespace Banco
{

    /// <summary>
    /// Summary description for BD_Parametro
    /// </summary>
    public class BD_Parametro
    {
        #region variaveis_globais

        acessaDB.conectaDBMSSQLServer sqlServer = new conectaDBMSSQLServer();
        string conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionLocal"];
        string usuario = string.Empty;

        #endregion

        #region construtor

        public BD_Parametro()
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
        }
        public BD_Parametro(string usu_Login)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
            usuario = usu_Login;
        }

        #endregion

        #region metodos

        public System.Data.DataSet _retorna_Parametro(
                                                string PARAMETRO_ID,
                                                string PRM_DESCRICAO,
                                                string PRM_INTERNO,
                                                string PRM_VALOR,
                                                string PRM_INSTRUCAO,
                                                string SOLICITANTE_ID
                                                )
        {

            if (string.IsNullOrEmpty(PARAMETRO_ID)) PARAMETRO_ID = "''";
            if (string.IsNullOrEmpty(PRM_DESCRICAO)) PRM_DESCRICAO = "''";
            if (string.IsNullOrEmpty(PRM_INTERNO)) PRM_INTERNO = "''";
            if (string.IsNullOrEmpty(PRM_VALOR)) PRM_VALOR = "''";
            if (string.IsNullOrEmpty(PRM_INSTRUCAO)) PRM_INSTRUCAO = "''";
            if (string.IsNullOrEmpty(SOLICITANTE_ID)) SOLICITANTE_ID = "''";

            string sql = "SELECT " +
                                  "PARAMETRO.PARAMETRO_ID, " +
                                  "PARAMETRO.PRM_DESCRICAO, " +
                                  "PARAMETRO.PRM_INTERNO, " +
                                  "PARAMETRO.PRM_VALOR, " +
                                  "PARAMETRO.PRM_INSTRUCAO, " +
                                  "PARAMETRO.SOLICITANTE_ID " +
                            "FROM PARAMETRO " +
                            "WHERE " +
                            "      (PARAMETRO.PARAMETRO_ID = " + PARAMETRO_ID + "  OR " + PARAMETRO_ID + " = '') AND " +
                            "      (PARAMETRO.PRM_DESCRICAO = " + PRM_DESCRICAO + "  OR " + PRM_DESCRICAO + " = '') AND " +
                            "      (PARAMETRO.PRM_INTERNO = " + PRM_INTERNO + "  OR " + PRM_INTERNO + " = '') AND " +
                            "      (PARAMETRO.PRM_VALOR = " + PRM_VALOR + "  OR " + PRM_VALOR + " = '') AND " +
                            "      (PARAMETRO.PRM_INSTRUCAO = " + PRM_INSTRUCAO + "  OR " + PRM_INSTRUCAO + " = '') AND " +
                            "      (PARAMETRO.SOLICITANTE_ID = " + SOLICITANTE_ID + "  OR " + SOLICITANTE_ID + " = '') ";

            return sqlServer.retornaDataSet(sql, conectionString);;
            

        }


        public List<ES_Parametro> _obter_Parametro(
                                                string PARAMETRO_ID,
                                                string PRM_DESCRICAO,
                                                string PRM_INTERNO,
                                                string PRM_VALOR,
                                                string PRM_PERCENTUAL,
                                                string PRM_INSTRUCAO,
                                                string SOLICITANTE_ID
                                                )
        {

            if (string.IsNullOrEmpty(PARAMETRO_ID)) PARAMETRO_ID = "0";
            if (string.IsNullOrEmpty(PRM_DESCRICAO)) PRM_DESCRICAO = "''"; else PRM_DESCRICAO = Util.retonarAspas(PRM_DESCRICAO + "%");
            if (string.IsNullOrEmpty(PRM_INTERNO)) PRM_INTERNO = "''";
            if (string.IsNullOrEmpty(PRM_VALOR)) PRM_VALOR = "0";
            if (string.IsNullOrEmpty(PRM_PERCENTUAL)) PRM_PERCENTUAL = "0";
            if (string.IsNullOrEmpty(PRM_INSTRUCAO)) PRM_INSTRUCAO = "''"; else PRM_INSTRUCAO = Util.retonarAspas(PRM_INSTRUCAO + "%");
            if (string.IsNullOrEmpty(SOLICITANTE_ID)) SOLICITANTE_ID = "0";

            string sql = "SELECT " +
                                  "PARAMETRO.PARAMETRO_ID, " +
                                  "PARAMETRO.PRM_DESCRICAO, " +
                                  "PARAMETRO.PRM_INTERNO, " +
                                  "PARAMETRO.PRM_VALOR, " +
                                  "PARAMETRO.PRM_PERCENTUAL, " +
                                  "PARAMETRO.PRM_INSTRUCAO, " +
                                  "PARAMETRO.SOLICITANTE_ID " +
                            "FROM PARAMETRO " +
                            "WHERE " +
                            "      (PARAMETRO.PARAMETRO_ID = " + PARAMETRO_ID + "  OR " + PARAMETRO_ID + " = 0) AND " +
                            "      (PARAMETRO.PRM_DESCRICAO LIKE " + PRM_DESCRICAO + "  OR " + PRM_DESCRICAO + " = '') AND " +
                            "      (PARAMETRO.PRM_INTERNO = " + PRM_INTERNO + "  OR " + PRM_INTERNO + " = '') AND " +
                            "      (PARAMETRO.PRM_VALOR = " + PRM_VALOR + "  OR " + PRM_VALOR + " = 0) AND " +
                            "      (PARAMETRO.PRM_PERCENTUAL = " + PRM_PERCENTUAL + "  OR " + PRM_PERCENTUAL + " = 0) AND " +
                            "      (PARAMETRO.PRM_INSTRUCAO LIKE " + PRM_INSTRUCAO + "  OR " + PRM_INSTRUCAO + " = '') AND " +
                            "      (PARAMETRO.SOLICITANTE_ID = " + SOLICITANTE_ID + "  OR " + SOLICITANTE_ID + " = 0) ";

            List<ES_Parametro> lista = new List<ES_Parametro>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Parametro item = new ES_Parametro();
                    item.PARAMETRO_ID = dr["PARAMETRO_ID"].ToString();
                    item.PRM_DESCRICAO = dr["PRM_DESCRICAO"].ToString();
                    item.PRM_INTERNO = (dr["PRM_INTERNO"].ToString() == "S")? true : false;
                    item.PRM_VALOR = dr["PRM_VALOR"].ToString();
                    item.PRM_PERCENTUAL = dr["PRM_PERCENTUAL"].ToString();
                    item.PRM_INSTRUCAO = dr["PRM_INSTRUCAO"].ToString();
                    item.SOLICITANTE_ID = dr["SOLICITANTE_ID"].ToString();
                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public ES_Parametro _obter_Parametro_by_id(
                                                string PARAMETRO_ID,
                                                string PRM_INSTRUCAO = "''"
                                                )
        {

            if (string.IsNullOrEmpty(PARAMETRO_ID)) PARAMETRO_ID = "0";
            if (!string.IsNullOrEmpty(PRM_INSTRUCAO)) PRM_INSTRUCAO = Util.retonarAspas(PRM_INSTRUCAO);

            string sql = "SELECT " +
                                  "PARAMETRO.PARAMETRO_ID, " +
                                  "PARAMETRO.PRM_DESCRICAO, " +
                                  "PARAMETRO.PRM_INTERNO, " +
                                  "PARAMETRO.PRM_VALOR, " +
                                  "PARAMETRO.PRM_PERCENTUAL, " +
                                  "PARAMETRO.PRM_INSTRUCAO, " +
                                  "PARAMETRO.SOLICITANTE_ID " +
                            "FROM PARAMETRO " +
                            "WHERE " +
                            "      (PARAMETRO.PARAMETRO_ID = " + PARAMETRO_ID + "  OR " + PARAMETRO_ID + " = '0') AND "+
                            "      (PARAMETRO.PRM_INSTRUCAO = " + PRM_INSTRUCAO + "  OR " + PRM_INSTRUCAO + " = '') ";

            ES_Parametro item = new ES_Parametro();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    item.PARAMETRO_ID = dr["PARAMETRO_ID"].ToString();
                    item.PRM_DESCRICAO = dr["PRM_DESCRICAO"].ToString();
                    item.PRM_INTERNO = (dr["PRM_INTERNO"].ToString() == "S") ? true : false;
                    item.PRM_VALOR = dr["PRM_VALOR"].ToString();
                    item.PRM_PERCENTUAL = dr["PRM_PERCENTUAL"].ToString();
                    item.PRM_INSTRUCAO = dr["PRM_INSTRUCAO"].ToString();
                    item.SOLICITANTE_ID = dr["SOLICITANTE_ID"].ToString();

                }
                return item;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public void _inserir_Parametro(
                                                string PARAMETRO_ID,
                                                string PRM_DESCRICAO,
                                                bool PRM_INTERNO,
                                                string PRM_VALOR,
                                                string PRM_PERCENTUAL,
                                                string PRM_INSTRUCAO,
                                                string SOLICITANTE_ID
                                                )
        {                       
            string sql = " INSERT INTO PARAMETRO " +
                         "      ( " +
                         
                         "          PRM_DESCRICAO, " +
                         "          PRM_INTERNO, " +
                         "          PRM_VALOR, " +
                         "          PRM_PERCENTUAL, " +
                         "          PRM_INSTRUCAO, " +
                         "          SOLICITANTE_ID  " +
                 "      )" +
                 "  VALUES " +
                 "      ( " +
                              
                              Util.retonarAspas(PRM_DESCRICAO, true) + ", " +
                              Util.retonarAspas(((PRM_INTERNO)? "S" : "N"), true) + ", " +
                              Util.retonarNull(PRM_VALOR) + ", " +
                              Util.retonarNull(PRM_PERCENTUAL) + ", " +
                              Util.retonarAspas(PRM_INSTRUCAO, true) + ", " +
                              Util.retonarNull(SOLICITANTE_ID) +
                 "      )";
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _atualizar_Parametro(
                                                string PARAMETRO_ID,
                                                string PRM_DESCRICAO,
                                                bool PRM_INTERNO,
                                                string PRM_VALOR,
                                                string PRM_PERCENTUAL,
                                                string PRM_INSTRUCAO,
                                                string SOLICITANTE_ID
                                                )
        {
            string sql = " UPDATE PARAMETRO SET " +
                         "      PRM_DESCRICAO = " + Util.retonarAspas(PRM_DESCRICAO, true) + ", ";
            
            //atualizar parametros do usuário
            if (!PRM_INTERNO)
                sql += "      PRM_INTERNO = " + Util.retonarAspas(((PRM_INTERNO) ? "S" : "N"), true) + ", ";

            sql +=
                         "      PRM_VALOR = " + Util.retonarNull(PRM_VALOR) + ", " +
                         "      PRM_PERCENTUAL = " + Util.retonarNull(PRM_PERCENTUAL) + ", " +
                         "      PRM_INSTRUCAO = " + Util.retonarAspas(PRM_INSTRUCAO, true) + ", " +
                         "      SOLICITANTE_ID = " + Util.retonarNull(SOLICITANTE_ID) +
                         " WHERE PARAMETRO_ID =  " + PARAMETRO_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _excluir_Parametro(string PARAMETRO_ID)
        {
            //Remover via interface somente parâmentros externos
            string sql = "DELETE FROM PARAMETRO WHERE ISNULL(PRM_INTERNO,'N') <> 'S' AND PARAMETRO_ID = " + PARAMETRO_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        #endregion
    }
}